package net.yla.smartbus.core.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @description:
 * @author: Yee
 * @date: 2021/3/24 14:50
 */
public class ExcelReader {
    /**
     * 根据fileType不同读取excel文件
     *
     * @param path
     * @param path
     * @throws IOException
     */
    public static List<List<String>> readExcel(String path) {
        String fileType = path.substring(path.lastIndexOf(".") + 1);
        // return a list contains many list
        List<List<String>> lists = new ArrayList<List<String>>();
        //读取excel文件
        InputStream is = null;
        try {
            is = new FileInputStream(path);
            //获取工作薄
            Workbook wb = null;
            if (fileType.equals("xls")) {
                wb = new HSSFWorkbook(is);
            } else if (fileType.equals("xlsx")) {
                wb = new XSSFWorkbook(is);
            } else {
                return null;
            }

            //读取第一个工作页sheet
            Sheet sheet = wb.getSheetAt(0);
            //第一行为标题
            for (Row row : sheet) {
                ArrayList<String> list = new ArrayList<String>();
                int flag = 0;
                System.out.println(">>>>>>>>>>>"+row.getLastCellNum());
                for (int i = 0; i < row.getLastCellNum(); i++) {
                    Cell cell = row.getCell(i);
                    //根据不同类型转化成字符串
                    //如果单元格是空（列）的跳过-----------合并的单元格值优先属于左上单元格
                    if(cell == null){
                        list.add("");
                    }else if(cell.getCellType() ==3){
                        list.add("");
                    }else if(cell.getCellType() ==0){
                        list.add(handleNumber(String.valueOf(cell.getNumericCellValue())));
                        flag = 1;
                    }else if(cell.getCellType() ==1){
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        list.add(cell.getStringCellValue());
                        flag = 1;
                    }else {
                        list.add("");
                    }
                }
                //如果整行都没有值，跳过整行
                if(list.size() != 0 && flag == 1){
                    lists.add(list);
                }

            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (is != null) {
                    is.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return lists;
    }

    /**
     * <b> 去掉数字字符串小数点后面无效的领，或者去掉小数点
     * </b><br><br><i>Description</i> :
     * @param number String
     * @return java.lang.String
     * <br><br>Date: 2019/11/14 11:17     <br>Author : dxl
     */
    public static String handleNumber(String number){

        if(number.contains(".") && number.substring(number.indexOf(".")+1,number.length()).length() == 16){
            if(number.substring(number.length()-1,number.length()).equals("1")){
                number = number.substring(0,number.length()-1);
            }else if(number.substring(number.length()-1,number.length()).equals("9")){
                Double tem = Double.valueOf(number) + 0.0000000000000002;
                number = tem.toString();
            }
        }
        if(!number.contains(".")){
            return number;
        }else if(number.lastIndexOf(".") == number.length()-1){
            number = number.substring(0,number.length()-1);
            return number;
        }else if(number.substring(number.length()-1,number.length()).equals("0")){
            number = number.substring(0,number.length()-1);
            number = handleNumber(number);
        }else {
            return number;
        }
        return number;
    }
}
